import sqlite3
import os
import json
from datetime import datetime

DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), '../data/name_records.db')

def init_database():
    """初始化SQLite数据库"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS name_records (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_input TEXT,
            results TEXT,
            create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    conn.commit()
    conn.close()

def save_name_record(user_data, results):
    """保存取名记录到数据库"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO name_records (user_input, results) VALUES (?, ?)",
        (json.dumps(user_data), json.dumps(results))
    )
    conn.commit()
    conn.close()

def get_history_records(limit=10):
    """获取历史记录（可选功能）"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute(
        "SELECT id, user_input, results, create_time FROM name_records ORDER BY create_time DESC LIMIT ?",
        (limit,)
    )
    records = cursor.fetchall()
    conn.close()
    
    formatted_records = []
    for record in records:
        id, user_input, results, create_time = record
        formatted_records.append({
            "id": id,
            "user_input": json.loads(user_input),
            "results": json.loads(results),
            "create_time": create_time
        })
    return formatted_records
